﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Threading.Tasks;

namespace Woodlands
{
    class Database
    {
        private static string MyConString = "SERVER=91.223.16.32;" +
                 "DATABASE=codeninja;" +
                 "UID=codeninja;" +
                 "PASSWORD=Password12!;";
        private static MySqlConnection connection = new MySqlConnection(MyConString);
        private static MySqlCommand command = connection.CreateCommand();
        private static bool Running = false;
        

        //Method used to login a member of staff
        public static async Task<bool> login(string userIn, string passwordIn, Staff x)
        {
            string enc = await Encryption.pass(passwordIn);
            MySqlDataReader Reader;
            Reader = await Task<MySqlDataReader>.Run(()=>query("select p.userID, p.firstname from person p JOIN staff s ON p.userID = s.userID_fk2 WHERE p.userID = '" + userIn +
                "' AND p.password = '" + enc + "' AND p.staff = true;"));
            while (Reader.Read())
            {
                x.FirstName = Reader.GetValue(1).ToString();
                connection.Close();
                return Task<bool>.Factory.StartNew(() => true).Result;
            }
            connection.Close();
            return Task<bool>.Factory.StartNew(() => false).Result;
        }

        public async static Task enrolStudent(String studentIDin)
        {
            await Task.Run(() => query("UPDATE student SET status = 'Live' WHERE userID_fk1 = '" + studentIDin + "'"));
            connection.Close();
            return;
        }

        public static void archiveStudent(String studentIDin)
        {
            query("UPDATE student SET status = 'Dormant' WHERE userID_fk1 = '" + studentIDin + "'");
            connection.Close();
        }

        public static void archiveStaff(String staffIDin)
        {
            query("UPDATE staff SET status = 'Dormant' WHERE userID_fk2 = '" + staffIDin + "'");
            connection.Close();
        }

        public static async Task<DataTable> searchModule(string searchType, string searchTerm)
        {
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter();
            if (searchType == "ID")
            {
                da = await Task<MySqlDataAdapter>.Run(() => new MySqlDataAdapter("SELECT concat(mc.moduleCode,CONVERT(m.moduleCodeID,CHAR(45))) AS 'moduleID', m.name FROM modules m " + 
                                                                                                  "JOIN moduleCodes mc "+
                                                                                                  "ON m.moduleCode_fk1 = mc.idmoduleCodes "+
                                                                                                  "WHERE concat(mc.moduleCode,m.moduleCodeID) REGEXP '"+searchTerm+"'", connection));
            }
            else if (searchType == "Name")
            {
                da = await Task<MySqlDataAdapter>.Run(() => new MySqlDataAdapter("SELECT concat(mc.moduleCode,CONVERT(m.moduleCodeID,CHAR(45))) AS 'moduleID', m.name FROM modules m " +
                                                                                                  "JOIN moduleCodes mc " +
                                                                                                  "ON m.moduleCode_fk1 = mc.idmoduleCodes " +
                                                                                                  "WHERE m.name REGEXP '" + searchTerm + "' AND m.moduleID <> 2", connection));
            }
            da.Fill(dt);
            connection.Close();
            return dt;
        }

        public static async Task<DataTable> searchStaff(string searchType, string searchString)
        {
            DataTable dt = new DataTable();
            if (searchType == "Staff ID")
            {
                searchType = "userID";
            }
            else if (searchType == "First Name")
            {
                searchType = "firstname";
            }
            else if (searchType == "Last Name")
            {
                searchType = "lastname";
            }
            MySqlDataAdapter da = await Task<MySqlDataAdapter>.Run(() => new MySqlDataAdapter("CALL searchStaff('" + searchType + "','" + searchString + "')", connection));
            await Task.Run(() =>
            {
                try
                {
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            });
            await Task.Run(() => connection.Close());

            return dt;
        }

        public static async Task<DataTable> searchStudent(string searchType, string searchString)
        {
            DataTable dt = new DataTable();
            if (searchType == "Student ID")
            {
                searchType = "userID";
            }
            else if (searchType == "First Name")
            {
                searchType = "firstname";
            }
            else if (searchType == "Last Name")
            {
                searchType = "lastname";
            }
                MySqlDataAdapter da = await Task<MySqlDataAdapter>.Run(() => new MySqlDataAdapter("CALL searchStudent('" + searchType + "','" + searchString + "')", connection));
                await Task.Run(() => 
                { 
                    try 
                    { 
                        da.Fill(dt); 
                    } 
                    catch (Exception ex) 
                    { 
                        Console.WriteLine(ex);
                    } 
                });
                await Task.Run(() => connection.Close());

            return dt;
        }

        public static MySqlDataAdapter fillCourse()
        {
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT idCourse, concat(CONVERT(idCourse USING utf8), ' - ',name) AS 'name' FROM courses WHERE idCourse != 2", connection);
            return da;
        }

        public static MySqlDataAdapter fillModuleCode(int x)
        {
            MySqlDataAdapter da = new MySqlDataAdapter();
            if (x == 0)
            {
                da = new MySqlDataAdapter("SELECT idmoduleCodes, moduleCode FROM moduleCodes WHERE moduleCode <> 'U' UNION SELECT 0, 'New...'", connection);
            }
            if (x == 1)
            {
                da = new MySqlDataAdapter("SELECT idmoduleCodes, moduleCode FROM moduleCodes WHERE moduleCode <> 'U'", connection);
            }
            return da;
        }

        public static async Task<string> newEmail(string firstname, string lastname)
        {
            if (Running)
            {
                return "";
            }
            Running = true;
            MySqlDataReader Reader;
            bool valid = true;
            int test = 2;
            string emailTest = firstname + "." + lastname + "@woodlandsuniversity.com";
            string front = firstname + "." + lastname;
            Reader = await Task<MySqlDataReader>.Run(() => query("SELECT emailUni FROM Person WHERE emailUni = '" + emailTest +"'"));
            while (Reader.Read())
            {
                valid = false;
            }
            await Task.Run(() => connection.Close());
            while (valid == false)
            {
                await Task.Run(() => connection.Close());
                bool innerTest = true;
                emailTest = firstname + "." + lastname + test + "@woodlandsuniversity.com";
                front = firstname + "." + lastname + test;
                Reader =  await Task<MySqlDataReader>.Run(() => query("SELECT emailUni FROM Person WHERE emailUni = '" + emailTest + "'"));
                while (Reader.Read())
                {
                    innerTest = false;
                }
                if (!innerTest)
                {
                    test++;
                    await Task.Run(() => connection.Close());
                    innerTest = true;
                    continue;
                }
                valid = true;
            }
            await Task.Run(() => connection.Close());
            Running = false;
            return front;

        }

        public static string nextModuleID(string x)
        {
            string nextID = "0";
            MySqlDataReader Reader;
            Reader = query("CALL nextModuleID('"+x+"')");
            while (Reader.Read())
            {
                nextID = Reader.GetValue(0).ToString();
            }
            connection.Close();
            return nextID;
        }

        public static string nextStudentID()
        {
            string nextID = "0";
            MySqlDataReader Reader;
            Reader = query("SELECT (userID + 1) AS 'NextID' FROM person WHERE userID < 99000000 ORDER BY userID DESC LIMIT 1");
            while (Reader.Read())
            {
                nextID = Reader.GetValue(0).ToString();
            }
            connection.Close();
            return nextID;
        }
        public static string nextStaffID()
        {
            string nextID = "0";
            MySqlDataReader Reader;
            Reader = query("SELECT (userID + 1) AS 'NextID' FROM person WHERE userID > 99000000 ORDER BY userID DESC LIMIT 1");
            while (Reader.Read())
            {
                nextID = Reader.GetValue(0).ToString();
            }
            connection.Close();
            return nextID;
        }

        public static async Task achiveModule(string code, string id)
        {
            await Task.Run(() => query("UPDATE modules SET status = 0 WHERE moduleCodeID = '" + id + "' AND moduleCode_fk1 = (SELECT idmoduleCodes FROM moduleCodes WHERE moduleCode = '" + code + "')", "UPDATE"));
            return;
        }

        public async static Task amendStudent(Student studentIn)
        {
            Student s = studentIn;
            await Task.Run(() => query("UPDATE person p JOIN student s SET p.firstName = '"+s.FirstName+"', p.middleName = '"+s.MiddleName+"', p.lastName = '"+s.LastName+"', "+
                                       "p.aLine1 = '"+s.hLine1+"', p.aLine2 = '"+s.hLine2+"', p.aCity = '"+s.hCity+"', p.aPostCode = '"+s.hPostCode+"', "+
                                       "p.phone = '"+s.HomePhone+"', p.mobile = '"+s.MobilePhone+"', p.emailPersonal = '"+s.EmailPersonal+"', "+
                                       "p.DOB = '"+s.DOB+"', s.tALine1 = '"+s.tLine1+"', s.tALine2 = '"+s.tLine2+"', s.tCity = '"+s.tCity+"', "+
                                       "s.tPostcode = '"+s.tPostCode+"', s.status = '"+s.Status+"' WHERE p.userID = "+s.ID, "UPDATE"));
            return;

        }

        public async static Task amendModule(Module moduleIn)
        {
            Module m = moduleIn;
            await Task.Run(() => query("UPDATE modules SET level='"+m.ModuleLevel+"', points='"+m.ModulePoints+"', name='"+m.Name+"', Ass1='"+m.AssessmentOneWeighting+"', Ass2='"+m.AssessmentTwoWeighting+"' " +
                "WHERE moduleCodeID="+m.ModuleID+" AND moduleCode_fk1 = (SELECT idmoduleCodes FROM moduleCodes WHERE moduleCode = '"+m.ModuleCode+"');","UPDATE"));
            return;
        }

        public async static Task newModuleCode(string codeIn)
        {
            await Task.Run(() => query("INSERT INTO moduleCodes(moduleCode) VALUES ('"+codeIn+"') ", "INSERT"));
            return;
        }

        public async static Task createModule(Module moduleIn)
        {
            Module m = moduleIn;
            await Task.Run(() => query("INSERT INTO modules(name,moduleCode_fk1,moduleCodeID) " + 
                                       "VALUES ('"+m.Name+"',(SELECT idmoduleCodes FROM moduleCodes WHERE moduleCode = '"+m.ModuleCode+"'),'"+m.ModuleID+"')","INSERT"));
            return;
        }

        public async static Task createStaff(Staff StaffIn)
        {
            Staff s = StaffIn;
            string password = await Encryption.pass(s.FirstName.ToLower()[0] + s.LastName.ToLower() + s.DOB.Substring(0, 4) + "=");
            string nextID = nextStaffID();
            await Task.Run(() => query("INSERT INTO person(userID,password,firstName,middleName,lastName,aLine1,aLine2,aCity,"
                          + "aPostcode,phone,mobile,emailUni,emailPersonal,DOB, staff) VALUES ("
                          + "'" + nextID + "','" + password + "','" + s.FirstName + "','" + s.MiddleName + "', '" + s.LastName + "','" + s.Line1 + "',"
                          + "'" + s.Line2 + "','" + s.City + "','" + s.Postcode + "','" + s.HomePhone + "',"
                          + "'" + s.MobilePhone + "','" + s.EmailUni + "','" + s.EmailPersonal + "','" + s.DOB + "','1')", "INSERT"));
            await Task.Run(() => query("INSERT INTO staff(userID_fk2,status) VALUES ('"+ nextID +"', 'Live')","INSERT"));
            return;
        }
        
        public async static Task createStudent(Student studentIn)
        {
            Student s = studentIn;
            string password = await Encryption.pass(s.FirstName.ToLower()[0] + s.LastName.ToLower() + s.DOB.Substring(0, 4) + "=");
            string nextID = nextStudentID();
            await Task.Run(() => query("INSERT INTO person(userID,password,firstName,middleName,lastName,aLine1,aLine2,aCity,"
                          + "aPostcode,phone,mobile,emailUni,emailPersonal,DOB) VALUES ("
                          + "'" + nextID + "','" + password + "','" + s.FirstName + "','" + s.MiddleName + "', '" + s.LastName + "','" + s.hLine1 + "',"
                          + "'" + s.hLine2 + "','" + s.hCity + "','" + s.hPostCode + "','" + s.HomePhone + "',"
                          + "'" + s.MobilePhone + "','" + s.EmailUni + "','" + s.EmailPersonal + "','" + s.DOB + "')", "INSERT"));
            await Task.Run(() => query("INSERT INTO student(userID_fk1,courseID_fk1,tAline1,tAline2,tCity,tPostcode) VALUES ("
                  + "'" + nextID + "','" + s.CourseID + "','" + s.tLine1 + "','" + s.tLine2 + "','" + s.tCity + "',"
                  + "'" + s.tPostCode + "')","INSERT"));
            //await Task.Run(() => query("INSERT INTO ","INSERT"));
            return;
        }

        public async static Task<Module> getModule(string code, string id)
        {
            MySqlDataReader Reader;
            Module m = new Module();
            Reader = await Task<MySqlDataReader>.Run(() => query("SELECT m.name, m.level, m.points, m.Ass1, m.Ass2, m.status FROM modules m " + 
                                                                 "JOIN moduleCodes mc ON m.moduleCode_fk1 = mc.idmoduleCodes " +
                                                                 "WHERE m.moduleCodeID = "+id+" AND mc.moduleCode = '"+code+"'"));
            while (Reader.Read())
            {
                m.ModuleID = id;
                m.ModuleCode = code;
                m.Name = Reader.GetValue(0).ToString();
                m.ModuleLevel = Reader.GetValue(1).ToString();
                m.ModulePoints = Reader.GetValue(2).ToString();
                m.AssessmentOneWeighting = Reader.GetValue(3).ToString();
                m.AssessmentTwoWeighting = Reader.GetValue(4).ToString();
                m.Status = Reader.GetValue(5).ToString();
            }
            connection.Close();
            return m;
        }

        public async static Task<Staff> getStaff(string staffID)
        {
            MySqlDataReader Reader;
            Staff s = new Staff();
            Reader = await Task<MySqlDataReader>.Run(() => query("SELECT p.firstname, p.middlename, p.lastname, p.phone, p.mobile, p.emailUni, p.Aline1, p.Aline2, p.aCity, p.aPostcode, s.status, p.emailPersonal, p.DOB FROM person p JOIN staff s ON p.userID = s.userID_fk2 WHERE p.userID = " + staffID));
            while (Reader.Read())
            {
                s.ID = staffID;
                s.FirstName = Reader.GetValue(0).ToString();
                s.MiddleName = Reader.GetValue(1).ToString();
                s.LastName = Reader.GetValue(2).ToString();
                s.HomePhone = Reader.GetValue(3).ToString();
                s.MobilePhone = Reader.GetValue(4).ToString();
                s.EmailUni = Reader.GetValue(5).ToString();
                s.Line1 = Reader.GetValue(6).ToString();
                s.Line2 = Reader.GetValue(7).ToString();
                s.City = Reader.GetValue(8).ToString();
                s.Postcode = Reader.GetValue(9).ToString();
                s.Status = Reader.GetValue(10).ToString();
                s.EmailPersonal = Reader.GetValue(11).ToString();
                s.DOB = Reader.GetValue(12).ToString();
            }
            connection.Close();
            return s;
        }

        public async static Task<Student> getStudent(string studentID)
        {
            MySqlDataReader Reader;
            Student s = new Student();
            Reader = await Task<MySqlDataReader>.Run(() => query("SELECT p.firstname, p.middlename, p.lastname, s.courseID_fk1, p.phone, p.mobile, p.emailUni, s.tALine1, s.tALine2, s.tCity, s.tPostcode, p.Aline1, p.Aline2, p.aCity, p.aPostcode, s.status, p.emailPersonal, p.DOB FROM person p JOIN student s ON p.userID = s.userID_fk1 WHERE p.userID = " + studentID));
            while (Reader.Read())
            {
                s.ID = studentID;
                s.FirstName = Reader.GetValue(0).ToString();
                s.MiddleName = Reader.GetValue(1).ToString();
                s.LastName = Reader.GetValue(2).ToString();
                s.CourseID = Convert.ToInt32(Reader.GetValue(3).ToString());
                s.HomePhone = Reader.GetValue(4).ToString();
                s.MobilePhone = Reader.GetValue(5).ToString();
                s.EmailUni = Reader.GetValue(6).ToString();
                s.tLine1 = Reader.GetValue(7).ToString();
                s.tLine2 = Reader.GetValue(8).ToString();
                s.tCity = Reader.GetValue(9).ToString();
                s.tPostCode = Reader.GetValue(10).ToString();
                s.hLine1 = Reader.GetValue(11).ToString();
                s.hLine2 = Reader.GetValue(12).ToString();
                s.hCity = Reader.GetValue(13).ToString();
                s.hPostCode = Reader.GetValue(14).ToString();
                s.Status = Reader.GetValue(15).ToString();
                s.EmailPersonal = Reader.GetValue(16).ToString();
                s.DOB = Reader.GetValue(17).ToString();
            }
            connection.Close();
            return s;
        }

        public static MySqlDataReader query(String sql)
        {
            MySqlDataReader Reader;
            command.CommandText = sql;
            try
            {
                connection.Open();
                Reader = command.ExecuteReader();
            }
            catch (MySqlException e)
            {
                System.Console.Out.WriteLine("Can not connect to database");
                Reader = null;
            }
            return Reader;
        }
        public static void query(String sql,String insert)
        {
            command.CommandText = sql;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}